1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmVoucherRecord
4 Sub fillVoucherNo()
5 Try
6 Dim CN As New SqlConnection(cs)
7 CN.Open()
8 adp = New SqlDataAdapter()
9 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(VoucherNo) FROM Voucher", CN)
10 ds = New DataSet("ds")
11 adp.Fill(ds)
12 dtable = ds.Tables(0)
13 cmbVoucherNo.Items.Clear()
14 For Each drow As DataRow In dtable.Rows
15 cmbVoucherNo.Items.Add(drow(0).ToString())
16 Next
17
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Public Sub GetData()
23 Try
24 con = New SqlConnection(cs)
25 con.Open()
26 cmd = New SqlCommand("Select RTRIM(Voucher.Id) as [Voucher ID], RTRIM(VoucherNo) as [Voucher No.],Convert(DateTime,Date,103) as [Voucher Date], RTRIM(Name) as [Name],RTRIM(Details) as [Details],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(Voucher.GrandTotal) as [Grand Total] from Voucher,SchoolInfo where Voucher.SchoolID=schoolInfo.S_ID order by Date", con)
27 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
28 Dim myDataSet As DataSet = New DataSet()
29 myDA.Fill(myDataSet, "Voucher")
30 dgw.DataSource = myDataSet.Tables("Voucher").DefaultView
31 con.Close()
32 Catch ex As Exception
33 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
34 End Try
35
36 End Sub
37 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
38 GetData()
39 fillVoucherNo()
40 End Sub
41 Sub Reset()
42 cmbVoucherNo.Text = ""
43 dtpDateFrom.Text = Today
44 dtpDateTo.Text = Now
45 GetData()
46 End Sub
47 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
48 Reset()
49 End Sub
50
51
52 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
53 Me.Close()
54 End Sub
55
56 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
57 Dim rowsTotal, colsTotal As Short
58 Dim I, j, iC As Short
59 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
60 Dim xlApp As New Excel.Application
61 Try
62 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
63 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
64 xlApp.Visible = True
65
66 rowsTotal = dgw.RowCount
67 colsTotal = dgw.Columns.Count - 1
68 With excelWorksheet
69 .Cells.Select()
70 .Cells.Delete()
71 For iC = 0 To colsTotal
72 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
73 Next
74 For I = 0 To rowsTotal - 1
75 For j = 0 To colsTotal
76 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
77 Next j
78 Next I
79 .Rows("1:1").Font.FontStyle = "Bold"
80 .Rows("1:1").Font.Size = 12
81
82 .Cells.Columns.AutoFit()
83 .Cells.Select()
84 .Cells.EntireColumn.AutoFit()
85 .Cells(1, 1).Select()
86 End With
87 Catch ex As Exception
88 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
89 Finally
90 'RELEASE ALLOACTED RESOURCES
91 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
92 xlApp = Nothing
93 End Try
94 End Sub
95
96 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
97 Try
98 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
99 Me.Hide()
100 frmVoucher.Show()
101 ' or simply use column name instead of index
102 'dr.Cells["id"].Value.ToString();
103 frmVoucher.txtVoucherID.Text = dr.Cells(0).Value.ToString()
104 frmVoucher.txtVoucherNo.Text = dr.Cells(1).Value.ToString()
105 frmVoucher.dtpDate.Text = dr.Cells(2).Value.ToString()
106 frmVoucher.txtName.Text = dr.Cells(3).Value.ToString()
107 frmVoucher.txtDetails.Text = dr.Cells(4).Value.ToString()
108 frmVoucher.txtSchoolID.Text = dr.Cells(5).Value.ToString()
109 frmVoucher.cmbSchoolName.Text = dr.Cells(6).Value.ToString()
110 frmVoucher.txtGrandTotal.Text = dr.Cells(7).Value.ToString()
111 frmVoucher.btnSave.Enabled = False
112 frmVoucher.btnDelete.Enabled = True
113 frmVoucher.btnUpdate.Enabled = True
114 frmVoucher.btnPrint.Enabled = True
115 frmVoucher.btnRemove.Enabled = False
116 con = New SqlConnection(cs)
117 con.Open()
118 Dim sql As String = "Select RTRIM(Particulars),RTRIM(Amount),RTRIM(Note) from Voucher,Voucher_OtherDetails where Voucher.Id=Voucher_OtherDetails.VoucherID and Voucher.ID=" & dr.Cells(0).Value & ""
119 cmd = New SqlCommand(sql, con)
120 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
121 frmVoucher.DataGridView1.Rows.Clear()
122 While (rdr.Read() = True)
123 frmVoucher.DataGridView1.Rows.Add(rdr(0), rdr(1), rdr(2))
124 End While
125 con.Close()
126 Catch ex As Exception
127 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
128 End Try
129
130 End Sub
131
132 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
133 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
134 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
135 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
136 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
137 End If
138 Dim b As Brush = SystemBrushes.ControlText
139 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
140
141 End Sub
142
143 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
144 Try
145 con = New SqlConnection(cs)
146 con.Open()
147 cmd = New SqlCommand("Select RTRIM(Voucher.Id) as [Voucher ID], RTRIM(VoucherNo) as [Voucher No.],Convert(DateTime,Date,103) as [Voucher Date], RTRIM(Name) as [Name],RTRIM(Details) as [Details],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(Voucher.GrandTotal) as [Grand Total] from Voucher,SchoolInfo where Voucher.SchoolID=schoolInfo.S_ID and Date between @d1 and @d2 order by Date", con)
148 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
149 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
150 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
151 Dim myDataSet As DataSet = New DataSet()
152 myDA.Fill(myDataSet, "Voucher")
153 dgw.DataSource = myDataSet.Tables("Voucher").DefaultView
154 con.Close()
155 Catch ex As Exception
156 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
157 End Try
158 End Sub
159
160 Private Sub cmbBillNo_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbVoucherNo.SelectedIndexChanged
161 Try
162 con = New SqlConnection(cs)
163 con.Open()
164 cmd = New SqlCommand("Select RTRIM(Voucher.Id) as [Voucher ID], RTRIM(VoucherNo) as [Voucher No.],Convert(DateTime,Date,103) as [Voucher Date], RTRIM(Name) as [Name],RTRIM(Details) as [Details],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(Voucher.GrandTotal) as [Grand Total] from Voucher,SchoolInfo where Voucher.SchoolID=schoolInfo.S_ID and VoucherNo='" & cmbVoucherNo.Text & "' order by Date", con)
165 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
166 Dim myDataSet As DataSet = New DataSet()
167 myDA.Fill(myDataSet, "Voucher")
168 dgw.DataSource = myDataSet.Tables("Voucher").DefaultView
169 con.Close()
170 Catch ex As Exception
171 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
172 End Try
173 End Sub
174 End Class
